﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BookModel;
using System.Data.SqlClient;

namespace DAL
{
    //读者借阅信息表
   
    public class borrowerIfnoDAL
    {
        #region 根据不同条件查询借阅记录
        public DataTable GetBorrwerBookReader(string ReadName,string Bookname)
        {
            DataTable dt = new DataTable();
           
            string sql = @"select C.BorrowerID,A.ReaderName,B.Bname,C.BorrowerDate,C.Borrowerreturn,C.Borrowerremark from ReaderInfo A, BookInfo B, borrowerIfno C
	where A.ReaderID=C.ReaderID and B.BID=C.Bid";
            //读者姓名不为null
            if (ReadName!=null)
            {
                SqlParameter[] sp = new SqlParameter[]
                {
                    new SqlParameter("@ReaderName","%"+ReadName+"%")
                 };
                sql += " and A.ReaderName like @ReaderName ";
                return dt = SQLHelp.FillTable(sql,CommandType.Text,sp);
            }
            //图书名称不为null
            if (Bookname!=null)
            {
                SqlParameter[] sp = new SqlParameter[]
                {
                     new SqlParameter("@Bname","%"+Bookname+"%")
                 };
                sql += " and B.Bname like @Bname ";
                return dt = SQLHelp.FillTable(sql, CommandType.Text, sp);
            }

            dt = SQLHelp.FillTable(sql);
            return dt;
        }
        #endregion
        #region 根据时间查询借阅记录
        public DataTable GetBorrwerData(string Date, string date1)
        {
            string sql = @"select C.BorrowerID,A.ReaderName,B.Bname,C.BorrowerDate,C.Borrowerreturn,C.Borrowerremark from ReaderInfo A, BookInfo B, borrowerIfno C where A.ReaderID=C.ReaderID and B.BID=C.Bid and BorrowerDate>=@Date and BorrowerDate<=@date1";
            DataTable dt = new DataTable();
            SqlParameter[] sp = new SqlParameter[2]
            {
                new SqlParameter("@Date",Date),
                 new SqlParameter("@date1",date1)
            };
            dt = SQLHelp.FillTable(sql,CommandType.Text,sp);
            return dt;
        }
        #endregion

        #region 查询借阅记录原表
        public List<borrowerIfno> GetBorrowerInfo()
        {
            string sql = "select *from borrowerIfno";
            List<borrowerIfno> lb = new List<borrowerIfno>();
           
            if (SQLHelp.OpenConn())
            { 
                SqlDataReader sdr = SQLHelp.ExecReader(sql);
                if (sdr!=null)
                {
                    while (sdr.Read())
                    {
                        lb.Add(new borrowerIfno
                            {
                                BorrowerID = (int)sdr["BorrowerID"],
                                ReaderID = (int)sdr["ReaderID"],
                                Bid = (int)sdr["Bid"],
                                BorrowerSate = sdr["BorrowerSate"] as string,
                                BorrowerreturnSate = sdr["BorrowerreturnSate"] as string,
                                BorrowerDate = Convert.ToDateTime(sdr["BorrowerDate"]),
                                Borrowerreturn = Convert.ToDateTime(sdr["Borrowerreturn"] as string),
                                BorrowerMake = sdr["BorrowerMake"] as string,
                                BorrowerMakeup = sdr["BorrowerMakeup"] as string,
                                Borrowerremark = sdr["Borrowerremark"] as string
                            });
                    }
                }
                SQLHelp.CloseConn();
            }
            return lb;
        }
        #endregion

        #region 增加借阅记录
        public string InsertBookBorrwer(borrowerIfno bsi)
        {
            string sql = @"insert into borrowerIfno values(@ReaderID,@Bid,@BorrowerSate,'完好','否',null,@BorrowerDate,null,@Borrowerremark)";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@ReaderID",bsi.ReaderID),
                new SqlParameter("@Bid",bsi.Bid),
                new SqlParameter("@BorrowerSate",bsi.BorrowerSate),
                new SqlParameter("@BorrowerDate",bsi.BorrowerDate),
                new SqlParameter("@Borrowerremark",bsi.Borrowerremark)
            };
            int count = SQLHelp.ExecQuery(sql,CommandType.Text,sp);
            switch (count)
            {
                case -2:
                    sql= "数据库链接失败,请联系您的管理员";
                    break;
                case -1:
                    sql= "数据输入有误，请检查输入";
                    break;
                case 0:
                    sql= "无任何修改";
                    break;
                default:
                    sql = "修改成功"+count+"条数据";
                    break;
            }
            return sql;
        }
        #endregion
        #region 修改借阅记录
        public string updateBookBorrwer(borrowerIfno bsi)
        {
            string sql = @"update borrowerIfno set Bid=@Bid,Borrowerremark=@Borrowerremark, Borrowerreturn=@Borrowerreturn,BorrowerreturnSate=@BorrowerreturnSate,BorrowerMakeup=@BorrowerMakeup,BorrowerMake=@BorrowerMake where BorrowerID=@BorrowerID";
            SqlParameter[] sp = new SqlParameter[]
            {
                 new SqlParameter("@BorrowerID",bsi.BorrowerID),
                new SqlParameter("@Bid",bsi.Bid),
                new SqlParameter("@Borrowerreturn",bsi.Borrowerreturn),
                new SqlParameter("@Borrowerremark",bsi.Borrowerremark),
                new SqlParameter("@BorrowerMake",bsi.BorrowerMake),
                 new SqlParameter("@BorrowerreturnSate",bsi.BorrowerreturnSate),
                new SqlParameter("@BorrowerMakeup",bsi.BorrowerMakeup)

            };
            int count = SQLHelp.ExecQuery(sql, CommandType.Text, sp);
            switch (count)
            {
                case -2:
                    sql= "数据库链接失败,请联系您的管理员";
                    break;
                case -1:
                    sql= "数据输入有误，请检查输入";
                    break;
                case 0:
                    sql= "无任何修改";
                    break;
                default:
                    sql = "还书成功"+count+"条数据，图书状态已更改";
                    break;
            }
            return sql;
        }
        #endregion

        public List<borrowerIfno> Gettborrower()
        {
            List<borrowerIfno> list = new List<borrowerIfno>();
            DataTable dt = new DataTable();
            dt = SQLHelp.FillTable("select * from [borrowerIfno]  where  BorrowerMakeup='是'");
            if ((dt != null) && (dt.Rows.Count > 0))
            {
                foreach (DataRow dr in dt.Rows)
                {
                    borrowerIfno borrow = new borrowerIfno();
                    borrow.BorrowerID = (int)dr["BorrowerID"];
                    borrow.ReaderID = (int)dr["ReaderID"];
                    borrow.Bid = (int)dr["Bid"];
                    borrow.BorrowerSate = dr["BorrowerSate"] as string;
                    borrow.BorrowerreturnSate = dr["BorrowerreturnSate"] as string;
                    borrow.BorrowerMakeup = dr["BorrowerMakeup"] as string;
                    borrow.BorrowerMake = dr["BorrowerMake"] as string;
                    borrow.BorrowerDate = Convert.ToDateTime(dr["BorrowerDate"]);
                    string Data = String.IsNullOrEmpty(dr["Borrowerreturn"].ToString()) ? dr["Borrowerreturn"] as string : dr["Borrowerreturn"].ToString();
                    borrow.Borrowerreturn = Convert.ToDateTime(Data);
                    borrow.Borrowerremark = dr["Borrowerremark"] as string;

                    list.Add(borrow);
                }
            }
            return list;
        }
        public List<borrowerIfno> selectBorrow(int id)
        {
            List<borrowerIfno> list = new List<borrowerIfno>();
            DataTable dt = new DataTable();
            dt = SQLHelp.FillTable("select * from [borrowerIfno] where [ReaderID] =" + id);
            if ((dt != null) && (dt.Rows.Count > 0))
            {
                foreach (DataRow dr in dt.Rows)
                {
                    borrowerIfno borrow = new borrowerIfno();
                    borrow.BorrowerID = (int)dr["BorrowerID"];
                    borrow.ReaderID = (int)dr["ReaderID"];
                    borrow.Bid = (int)dr["Bid"];
                    borrow.BorrowerSate = dr["BorrowerSate"] as string;
                    borrow.BorrowerreturnSate = dr["BorrowerreturnSate"] as string;
                    borrow.BorrowerMakeup = dr["BorrowerMakeup"] as string;
                    borrow.BorrowerMake = dr["BorrowerMake"] as string;
                    borrow.BorrowerDate = Convert.ToDateTime(dr["BorrowerDate"]);
                    string Data = String.IsNullOrEmpty(dr["Borrowerreturn"].ToString()) ? dr["Borrowerreturn"] as string : dr["Borrowerreturn"].ToString();
                    borrow.Borrowerreturn = Convert.ToDateTime(Data);
                    borrow.Borrowerremark = dr["Borrowerremark"] as string;

                    list.Add(borrow);
                }
            }
            return list;
        }

        //赔偿信息
        public DataTable SelectDamages(int BorrowerID)
        {
            DataTable dt = new DataTable();
            string sql = @"select B.BorrowerID,C .Bname,A .ReaderName,C .BPrice from ReaderInfo A,borrowerIfno B,BookInfo C
WHERE  A.ReaderID=B .ReaderID AND B .Bid=C .BID and BorrowerID='"+BorrowerID+"'";
            dt = SQLHelp.FillTable(sql);
            return dt;
        }
        //修改赔偿信息
        public string updateBorrwer(borrowerIfno bsi)
        {
            string sql = @"update borrowerIfno set Borrowerremark=@Borrowerremark,BorrowerMake=@BorrowerMake where BorrowerID=@BorrowerID";
            SqlParameter[] sp = new SqlParameter[]
            {
                 new SqlParameter("@BorrowerID",bsi.BorrowerID),
                new SqlParameter("@Borrowerremark",bsi.Borrowerremark),
                new SqlParameter("@BorrowerMake",bsi.BorrowerMake),
            };
            int count = SQLHelp.ExecQuery(sql, CommandType.Text, sp);
            switch (count)
            {
                case -2:
                    sql= "数据库链接失败,请联系您的管理员";
                    break;
                case -1:
                    sql= "数据输入有误，请检查输入";
                    break;
                case 0:
                    sql= "无任何修改";
                    break;
                default:
                    sql="还书成功,图书状态未更新";
                    break;
            }
            return sql;
        }
    }
}
